﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

public class DbHelperSQL
{
    public static string strPubConnectionEpicor = "Data Source={0};Initial Catalog={1};persist security info=False;User ID={2};Password={3}";
    //public static string strPubConnectionEpicor = "Data Source=192.168.100.250;Initial Catalog=EPICOR10;persist security info=False;User ID=szmis;Password=szmis";
    //public static string strPubConnectionEpicor = "Data Source=192.168.100.250;Initial Catalog=E101600TEST;persist security info=False;User ID=szmis;Password=szmis";
    //public static string strPubConnectionEpicor = "Data Source=192.168.100.250;Initial Catalog=EPICORPILOT;persist security info=False;User ID=szmis;Password=szmis";
    //public static string strPubConnectionEpicor = "Data Source=10.0.3.186;Initial Catalog=EPICORPILOT1;persist security info=False;User ID=epicor;Password=epicor";

    public static Exception ExecSql(string strSql, string strSqlConnection)
    {
        Exception strError = null;
        System.Data.SqlClient.SqlTransaction objSqlTran = null;
        try
        {
            System.Data.SqlClient.SqlConnection objSqlCn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand objSqlCmd = null;
            objSqlCn.ConnectionString = strSqlConnection;
            objSqlCn.Open();
            objSqlTran = objSqlCn.BeginTransaction();
            objSqlCmd = objSqlCn.CreateCommand();
            objSqlCmd.Transaction = objSqlTran;
            objSqlCmd.CommandTimeout = 0;
            objSqlCmd.CommandText = strSql;
            objSqlCmd.ExecuteNonQuery();
            objSqlTran.Commit();
            if (objSqlCn.State == ConnectionState.Open) objSqlCn.Close();
            strError = null;
        }
        catch (Exception ex)
        {
            objSqlTran.Rollback(); throw ex;
        }
        return strError;
    }

    public static object ExecReadField(string strSql, string strSqlConnection)
    {
        object objValue = null;
        try
        {
            System.Data.SqlClient.SqlConnection objSqlCn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand objSqlCmd = null;
            System.Data.SqlClient.SqlDataReader objSqlRead = null;
            objSqlCn.ConnectionString = strSqlConnection;
            objSqlCn.Open();
            objSqlCmd = objSqlCn.CreateCommand();
            objSqlCmd.CommandTimeout = 0;
            objSqlCmd.CommandText = strSql;
            objSqlRead = objSqlCmd.ExecuteReader();
            objValue = null;
            while (objSqlRead.Read())
            {
                objValue = objSqlRead[0];
            }
            if (objSqlCn.State == ConnectionState.Open)
            {
                objSqlRead.Close();
                objSqlCn.Close();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return objValue;
    }

    /// <summary>
    /// 查询SQL语句及返回DataSet
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns></returns>
    public static DataSet QueryDataSet(string SQLString)
    {
        return QueryDataSet(strPubConnectionEpicor, SQLString);
    }

    /// <summary>
    /// 查询SQL语句及返回DataSet
    /// </summary>
    /// <param name="ConnID">连接字符串</param>
    /// <param name="SQLString">SQL语句</param>
    /// <returns></returns>
    public static DataSet QueryDataSet(string ConnID, string SQLString)
    {
        return QueryDataSet(ConnID, SQLString, "");
    }

    /// <summary>
    /// 查询SQL语句及返回DataSet
    /// </summary>
    /// <param name="ConnID">连接字符串</param>
    /// <param name="SQLString">SQL语句</param>
    /// <param name="srcTable">表名</param>
    /// <returns></returns>
    public static DataSet QueryDataSet(string ConnID, string SQLString, string strTable)
    {
        if (string.IsNullOrEmpty(ConnID)) return null;
        string connectionString = ConnID;
        DataSet dtDataSet = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(SQLString, connection);
                adapter.SelectCommand.CommandTimeout = 0;
                adapter.Fill(dtDataSet, "ds");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            dtDataSet.RemotingFormat = SerializationFormat.Binary;
            connection.Close();
            adapter = null;
        }
        for (int i = 0; i < dtDataSet.Tables.Count - 1; i++)
        {
            if (i < (dtDataSet.Tables.Count - 1))
            {
                dtDataSet.Tables[i].TableName = "ds" + (i + 1).ToString();
            }
            else
            {
                dtDataSet.Tables[i].TableName = strTable;
            }
        }
        return dtDataSet;
    }

}

